﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookClubIt.DTO;
using System.Data.SqlClient;
using BookClubIt.Data.Commands;
using System.Data;

namespace BookClubIt.Data
{
   public class BookClubData
    {
        public BookClubs GetActiveBookClubs()
        {
            return GetActiveBookClubs(false);
        }
        public BookClubs GetActiveBookClubs(bool getCurrentBook)
        {
            BookClubs clubs = new BookClubs();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, @"SELECT bc.*
                FROM Book_Club bc
                WHERE bc.Club_Type_Id != 3 ORDER BY bc.Name"))
            {
                while (reader.Read())
                {
                    clubs.Add(BuildBookClub(reader));

                }
            }

            return clubs;
        }

        public BookClubs SearchBookClubsByTitle(string title)
        {
            BookClubs clubs = new BookClubs();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT * FROM Book_Club WHERE Name Like '%{0}%'", title)))
            {
                while (reader.Read())
                {
                    clubs.Add(BuildBookClub(reader));

                }
            }

            return clubs;
        }
        public BookClubs GetTopBookClubs()
        {
            BookClubs clubs = new BookClubs();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, @"SELECT TOP 5 bc.*
                FROM Book_Club bc
                WHERE bc.Club_Type_Id != 3 ORDER BY bc.Created_Date DESC"))
            {
                while (reader.Read())
                {
                    clubs.Add(BuildBookClub(reader));

                }
            }

            return clubs;
        }
     
        public BookClubs GetPopularBookClubs()
        {
            BookClubs clubs = new BookClubs();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, @"SELECT TOP 5 bc.*, COUNT(mbc.Book_Club_Id) AS Member_Count
                FROM Book_Club bc
                JOIN Member_Book_Club AS mbc ON mbc.Book_Club_Id = bc.Id
                WHERE bc.Club_Type_Id != 3 
                GROUP BY bc.Id, bc.Name, bc.Club_Type_Id, bc.Genre_Id,bc.Created_Date, bc.Club_Description, bc.Rules,bc.Website,bc.AdultGroup
                ,bc.NonLeaderAddEvents,bc.NonLeaderAddPolls,bc.NonLeaderAddTopics,bc.GroupMeetsRealLife,bc.AvatarURL
                ORDER BY Member_Count DESC"))
            {
                while (reader.Read())
                {
                    clubs.Add(BuildBookClub(reader));

                }
            }

            return clubs;

        }
        public BookClubs GetMemberBookClubs(int uid)
        {
            BookClubs clubs = new BookClubs();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT bc.*, m.Id userid
                FROM Book_Club bc
                LEFT JOIN Member_Book_Club mbc ON bc.Id = mbc.Book_Club_Id
LEFT JOIN Member m ON mbc.Member_Id = m.Id
                WHERE bc.Club_Type_Id != 3
AND m.Id = {0} ORDER BY bc.Name", uid)))
            {
                while (reader.Read())
                {
                    clubs.Add(BuildBookClub(reader));

                }
            }

            return clubs;
        }
        public BookClubs GetBookClubsByGenre(int gid)
        {
            BookClubs clubs = new BookClubs();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT * FROM Book_Club WHERE Genre_Id = {0} AND Club_Type_Id != 3", gid)))
            {
                while (reader.Read())
                {
                    clubs.Add(BuildBookClub(reader));

                }
            }

            return clubs;
        }

        public BookClubs GetBookClubsByBookReading(int bid)
        {
            BookClubs clubs = new BookClubs();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT * FROM Book_Club bc
JOIN Book_In_Club bic ON bc.Id = bic.Book_Club_Id
WHERE bic.Book_Id = {0}
AND bic.Is_Current = 1
AND bc.Club_Type_Id != 3", bid)))
            {
                while (reader.Read())
                {
                    clubs.Add(BuildBookClub(reader));

                }
            }

            return clubs;
        }

        public Meetings GetMeetings(int cid)
        {
            Meetings meetings = new Meetings();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT * FROM Meeting WHERE Book_Club_Id = {0}", cid)))
            {
                while (reader.Read())
                {
                    meetings.Add(BuildMeeting(reader));

                }
            }

            return meetings;
        }

        public Meeting GetMeetingById(int mid)
        {
            Meeting meeting = new Meeting();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT * FROM Meeting WHERE Id = {0}", mid)))
            {
                if (reader.Read())
                {
                    meeting = BuildMeeting(reader);
                }
            }

            return meeting;
        }

        public Activities GetActivityById(int cid)
        {
            Activities activities = new Activities();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT * FROM Activity WHERE Book_Club_Id = {0} ORDER BY Time DESC",cid)))
            {
                while (reader.Read())
                {
                    activities.Add(BuildActivity(reader));

                }
            }

            return activities;
        }

        public Activities GetTopActivityById(int cid)
        {
            Activities activities = new Activities();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT TOP 5 * FROM Activity WHERE Book_Club_Id = {0} ORDER BY Time DESC", cid)))
            {
                while (reader.Read())
                {
                    activities.Add(BuildActivity(reader));

                }
            }

            return activities;
        }
        public BookClub GetBookClubById(int id)
        {
            return GetBookClubById(id, false);
        }

        public BookClub GetBookClubById(int id, bool getCurrentBook)
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT bc.*, bic.Book_Id
                FROM Book_Club bc
                LEFT JOIN Book_In_Club bic ON bc.Id = bic.Book_Club_Id
                where bc.Id = {0}", id)))
            {
                if (reader.Read())
                {
                    club = BuildBookClub(reader);
                }
            }

            return club;
        }


        public void ChangeBookInClubById(int cid, int bid)
        {
            try
            {
                BciDatabase db = new BciDatabase("Bookclub");
                SqlCommand command = BookClubCommandList.UpdateCurrentBook;
                command.Parameters["@Bookclub_Id"].Value = cid;
                command.Parameters["@Book_Id"].Value = bid;
                db.ExecuteNonQuery(command);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return;
        }

        public void ApprovePendingMember(int cid, int uid)
        {
            try
            {
                BciDatabase db = new BciDatabase("Bookclub");
                SqlCommand command = BookClubCommandList.ApprovePendingMember;
                command.Parameters["@Bookclub_Id"].Value = cid;
                command.Parameters["@User_Id"].Value = uid;
                db.ExecuteNonQuery(command);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return;
        }
        public void DenyPendingMember(int cid, int uid)
        {
            try
            {
                BciDatabase db = new BciDatabase("Bookclub");
                SqlCommand command = BookClubCommandList.DenyPendingMember;
                command.Parameters["@Bookclub_Id"].Value = cid;
                command.Parameters["@User_Id"].Value = uid;
                db.ExecuteNonQuery(command);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return;
        }
        public void SendBookClubMessage(int cid, int senderuid, int recieveruid, string message)
        {
            try
            {
                BciDatabase db = new BciDatabase("Bookclub");
                SqlCommand command = BookClubCommandList.SendBookClubMessage;
                command.Parameters["@Bookclub_Id"].Value = cid;
                command.Parameters["@Sender_User_Id"].Value = senderuid;
                command.Parameters["@Reciever_User_Id"].Value = recieveruid;
                command.Parameters["@Message"].Value = message;
                command.Parameters["@Sent_Date"].Value = DateTime.Now;
                db.ExecuteNonQuery(command);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return;
        }

        public void DeleteBookClub(int cid)
        {
            try
            {
                BciDatabase db = new BciDatabase("Bookclub");
                SqlCommand command = BookClubCommandList.DeleteBookClub;
                command.Parameters["@Bookclub_Id"].Value = cid;
                db.ExecuteNonQuery(command);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return;
        }

        public void AddClubActivity(int cid, int uid, string type, object data)
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            SqlCommand command = new SqlCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "INSERT INTO [Activity] (User_Id, Book_Club_Id, Type, Data, Time) VALUES ('" + uid + "', '" + cid + "', '" + type + "','" + data + "','"+DateTime.Now+"')";
            db.ExecuteNonQuery(command);
        }

        public void AddBookToClubShelf(int cid, int bid)
        {
            try
            {
                BciDatabase db = new BciDatabase("Bookclub");
                SqlCommand command = BookClubCommandList.AddBookToClubShelf;
                command.Parameters["@Bookclub_Id"].Value = cid;
                command.Parameters["@Book_Id"].Value = bid;
                db.ExecuteNonQuery(command);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return;
        }

        public void DeleteTopicComment(int tid)
        {
            try
            {
                BciDatabase db = new BciDatabase("Bookclub");
                SqlCommand command = BookClubCommandList.DeleteTopicComment;
                command.Parameters["@Topic_Id"].Value = tid;
                db.ExecuteNonQuery(command);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return;
        }

        public void RemoveBookFromClubShelf(int cid, int bid)
        {
            try
            {
                BciDatabase db = new BciDatabase("Bookclub");
                SqlCommand command = BookClubCommandList.RemoveBookFromClubShelf;
                command.Parameters["@Bookclub_Id"].Value = cid;
                command.Parameters["@Book_Id"].Value = bid;
                db.ExecuteNonQuery(command);

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return;
        }

        public BookClub JoinBookClub(int cid, int uid, bool approved)
        {
            int isapproved = 0;
            if (approved == true)
                isapproved = 1;

            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"INSERT INTO Member_Book_Club (Member_Id, Book_Club_Id, Joined_Date, Is_Approved)
            VALUES ('{0}', '{1}', '{2}','{3}')", uid, cid, DateTime.Now, isapproved)))
            {
                if (reader.Read())
                {
                    // club = BuildBookClub(reader, getCurrentBook);
                }
            }

            return club;
        }

    

        public BookClub AddLeaderToClub(int bid, int uid)
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"INSERT INTO Leader_Book_Club (Member_Id, Book_Club_Id, Time)
            VALUES ('{0}', '{1}', '{2}')", uid, bid, DateTime.Now)))
            {
                if (reader.Read())
                {
                    // club = BuildBookClub(reader, getCurrentBook);
                }
            }

            return club;
        }

        public BookClub AddAdminToClub(int cid, int uid)
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"INSERT INTO Admin_Book_Club (User_Id, Book_Club_Id, Time)
            VALUES ('{0}', '{1}', '{2}')", uid, cid, DateTime.Now)))
            {
                if (reader.Read())
                {
                    // club = BuildBookClub(reader, getCurrentBook);
                }
            }

            return club;
        }


        public BookClub RemoveMemberFromClub(int cid, int uid)
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"DELETE FROM Member_Book_Club WHERE Member_Id = {0} AND Book_Club_Id = {1}", uid, cid)))
            {
                if (reader.Read())
                {
                    // club = BuildBookClub(reader, getCurrentBook);
                }
            }

            return club;
        }

        public BookClub RemoveLeaderFromClub(int cid, int uid)
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"DELETE FROM Leader_Book_Club WHERE Member_Id = {0} AND Book_Club_Id = {1}", uid, cid)))
            {
                if (reader.Read())
                {
                    // club = BuildBookClub(reader, getCurrentBook);
                }
            }

            return club;
        }

        public BookClub LeaveBookClub(int bid, int uid)
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("DELETE FROM Member_Book_Club WHERE Member_Id = '{0}' AND Book_Club_Id = '{1}'", uid, bid)))
            {
                if (reader.Read())
                {
                    // club = BuildBookClub(reader, getCurrentBook);
                }
            }

            return club;
        }


        public ClubType GetBookClubType(int ctid)
        {
            ClubType type = new ClubType();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Club_Type WHERE Id = '{0}'", ctid)))
            {
                if (reader.Read())
                {
                    type = BuildType(reader);
                }
            }
            return type;
        }


        public GenreType GetBookClubGenre(int gtid)
        {
            GenreType type = new GenreType();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Genre_Type WHERE Id = '{0}'", gtid)))
            {
                if (reader.Read())
                {
                    type = BuildGenre(reader);
                }
            }
            return type;

        }
        public BookClub GetClubTypes()
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Club_Type")))
            {
                if (reader.Read())
                {
                    // club = BuildBookClub(reader, getCurrentBook);
                }
            }

            return club;
        }
        public GenreTypes GetGenreTypes()
        {
            GenreTypes genre_types = new GenreTypes();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Genre_Type")))
            {
                while (reader.Read())
                {
                    genre_types.Add(BuildGenre(reader));

                }
            }

            return genre_types;
        }
        //public GenreTypes GetBookClubGenres()
        //{
        //    GenreTypes types = new GenreTypes();
        //    BciDatabase db = new BciDatabase("Bookclub");
        //    using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Genre_Type")))
        //    {
        //        while (reader.Read())
        //        {
        //            types = BuildGenre(reader);
        //        }
        //    }
        //    return types;

        //}
        public Topics GetDiscussionTopics(int bid, bool current)
        {
           int getCurrent = 0;
            Topics topics = new Topics();
            BciDatabase db = new BciDatabase("Bookclub");
            if (current == true)
            {
                getCurrent = 1;
            }
            else
            {
                getCurrent = 0;
            }
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT t.*,t.Book_Club_Id bid
FROM Topic t
JOIN Book_In_Club bic ON t.Book_Id = bic.Book_Id
WHERE bic.Is_Current = {0}
AND bic.Book_Club_Id = {1}", getCurrent, bid)))
            {
                while (reader.Read())
                {
                    topics.Add(BuildTopic(reader));
                }
                return topics;
            }
        }

        public Comments GetTopicComments(int tid)
        {
            Comments comments = new Comments();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT m.Username,td.* FROM Topic_Discussion td
 LEFT JOIN Member m ON td.User_Id = m.Id WHERE td.Topic_Id = {0}  ORDER BY Comment_Date ASC", tid)))
            {
                while (reader.Read())
                {
                    comments.Add(BuildComment(reader));
                }
                return comments;
            }
        }

        public Comment GetLatestComment(int tid)
        {
            Comment comment = new Comment();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"SELECT TOP 1 td.* FROM Topic_Discussion td
            WHERE td.Topic_Id = {0}
            Order by Comment_Date DESC", tid)))
            {
                while (reader.Read())
                {
                    comment = BuildComment(reader);
                }
                return comment;
            }
        }

        public Topic GetDiscussionTopic(int bid, int tid)
        {
            Topic topic = new Topic();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Topic WHERE Book_Club_Id = {0} AND Id = {1}", bid, tid)))
            {
                while (reader.Read())
                {
                    topic = BuildTopic(reader);
                }
                return topic;
            }
        }

        public Topic GetDiscussionTopicById(int tid)
        {
            Topic topic = new Topic();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Topic WHERE Id = {0}", tid)))
            {
                while (reader.Read())
                {
                    topic = BuildTopic(reader);
                }
                return topic;
            }
        }


        public int CreateTopicComment(int tid, int uid, string comment)
        {
            int comid = 0;
            try
            {
                BciDatabase db = new BciDatabase("Bookclub");
                SqlCommand command = BookClubCommandList.CreateTopicComment;
                command.Parameters["@Topic_Id"].Value = tid;
                command.Parameters["@User_Id"].Value = uid;
                command.Parameters["@Topic_Comment"].Value = comment;
                command.Parameters["@Comment_Date"].Value = DateTime.Now;
                db.ExecuteNonQuery(command);

             comid = Convert.ToInt32(command.Parameters["Id"].Value.ToString());
            }

            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return comid;

        }

        //public int CreateTopic(int cid, int bid, string topic, string asin)
        //{
        //    int tid = 0;
        //    try
        //    {
        //        BciDatabase db = new BciDatabase("Bookclub");
        //        SqlCommand command = BookClubCommandList.CreateTopic;
        //        command.Parameters["@Book_Club_Id"].Value = cid;
        //        command.Parameters["@Book_Id"].Value = bid;
        //        command.Parameters["@Topic_Name"].Value = topic;
        //        command.Parameters["@ASIN"].Value = asin;
        //        command.Parameters["@Created_Date"].Value = DateTime.Now;
        //        db.ExecuteNonQuery(command);

        //        comid = Convert.ToInt32(command.Parameters["Id"].Value.ToString());
        //    }

        //    catch (Exception ex)
        //    {
        //        Console.WriteLine(ex.Message);
        //    }

        //    return tid;

        //}

        public BookClub CreateTopic(int cid, int bid, string topic, string asin)
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format(@"INSERT INTO Topic (Book_Club_Id, Book_Id, Topic_Name, ASIN, Created_Date)
            VALUES ('{0}','{1}','{2}','{3}','{4}')", cid, bid, topic, asin, DateTime.Now)))
            {
                if (reader.Read())
                {
                    // club = BuildBookClub(reader, getCurrentBook);
                }
            }

            return club;
        }

        public bool IsLeader(int uid, int bid)
        {
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Leader_Book_Club WHERE Member_Id = {0} AND Book_Club_Id = {1}", uid, bid)))
            {
                if (reader.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }
        public bool IsAdmin(int uid, int cid)
        {
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Admin_Book_Club WHERE User_Id = {0} AND Book_Club_Id = {1}", uid, cid)))
            {
                if (reader.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public bool IsBookClubMember(int uid, int cid)
        {
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Member_Book_Club WHERE Member_Id = {0} AND Book_Club_Id = {1} AND Is_Approved = 1", uid, cid)))
            {
                if (reader.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public bool IsPendingBookClubMember(int uid, int cid)
        {
            BciDatabase db = new BciDatabase("Bookclub");
            using (SqlDataReader reader = db.ExecuteReader(CommandType.Text, string.Format("SELECT * FROM Member_Book_Club WHERE Member_Id = {0} AND Book_Club_Id = {1} AND Is_Approved = 0", uid, cid)))
            {
                if (reader.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public BookClub UpdateBookClubAvatar(int cid, string filename)
        {
            BookClub club = new BookClub();
            BciDatabase db = new BciDatabase("Bookclub");
            string UpdateAvatarQuery = "UPDATE Book_Club SET AvatarURL = '" + filename + "' WHERE Id = " + cid + "";

            SqlCommand updateavatar = new SqlCommand(UpdateAvatarQuery);

            SqlDataReader reader;
            reader = db.ExecuteReader(updateavatar);

            return club;
        }

        private BookClub BuildBookClub(SqlDataReader reader)
        {
            BookClub club = new BookClub
            {
              
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                Name = ConvertDBNull.To<string>(reader["Name"], string.Empty),
                ClubType = ConvertDBNull.To<int>(reader["Club_Type_Id"], 0),
                GenreType = ConvertDBNull.To<int>(reader["Genre_Id"], 0),
                CreatedDate = ConvertDBNull.To<DateTime>(reader["Created_Date"], DateTime.MinValue),
                Description = ConvertDBNull.To<string>(reader["Club_Description"], string.Empty),
                Rules = ConvertDBNull.To<string>(reader["Rules"], string.Empty),
                Website = ConvertDBNull.To<string>(reader["Website"], string.Empty),
                AdultGroup = ConvertDBNull.To<int>(reader["AdultGroup"], 0),
                NonLeaderAddEvents = ConvertDBNull.To<int>(reader["NonLeaderAddEvents"], 0),
                NonLeaderAddPolls = ConvertDBNull.To<int>(reader["NonLeaderAddPolls"], 0),
                NonLeaderAddTopics = ConvertDBNull.To<int>(reader["NonLeaderAddTopics"], 0),
                GroupMeetsRealLife = ConvertDBNull.To<int>(reader["GroupMeetsRealLife"], 0),
                AvatarURL = ConvertDBNull.To<string>(reader["AvatarURL"], string.Empty),
            };

            //if (getCurrentBook)
             //  club.CurrentBook = GetBookById(ConvertDBNull.To<int>(reader["Book_Id"], 0));
            return club;

        }

        private Topic BuildTopic(SqlDataReader reader)
        {
            return new Topic
            {
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                Book_Club_Id = ConvertDBNull.To<int>(reader["Id"], 0),
                Book_Id = ConvertDBNull.To<int>(reader["Book_Id"], 0),
                Topic_Name = ConvertDBNull.To<string>(reader["Topic_Name"], string.Empty),
                ASIN = ConvertDBNull.To<string>(reader["ASIN"], string.Empty),
                Created_Date = ConvertDBNull.To<DateTime>(reader["Created_Date"], DateTime.MinValue),
            };
        }

        private Comment BuildComment(SqlDataReader reader)
        {
            return new Comment
            {
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                Topic_Id = ConvertDBNull.To<int>(reader["Topic_Id"], 0),
                User_Id = ConvertDBNull.To<int>(reader["User_Id"], 0),
                Topic_Comment = ConvertDBNull.To<string>(reader["Topic_Comment"], string.Empty),
                Comment_Date = ConvertDBNull.To<DateTime>(reader["Comment_Date"], DateTime.MinValue),
            };
        }

        private ClubType BuildType(SqlDataReader reader)
        {
            return new ClubType
            {
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                ClubTypeName = ConvertDBNull.To<string>(reader["Club_Type_Name"], string.Empty),
            };

        }

        private GenreType BuildGenre(SqlDataReader reader)
        {
            return new GenreType
            {
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                GenreName = ConvertDBNull.To<string>(reader["Genre_Name"], string.Empty),
            };

        }

        private Activity BuildActivity(SqlDataReader reader)
        {
            return new Activity
            {
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                User_Id = ConvertDBNull.To<int>(reader["User_Id"], 0),
                Book_Club_Id = ConvertDBNull.To<int>(reader["Book_Club_Id"], 0),
                Type = ConvertDBNull.To<string>(reader["Type"], string.Empty),
                Data = ConvertDBNull.To<int>(reader["Data"], 0),
                Time = ConvertDBNull.To<DateTime>(reader["Time"], DateTime.MinValue),
            };

        }

        private Meeting BuildMeeting(SqlDataReader reader)
        {
            return new Meeting
            {
                Id = ConvertDBNull.To<int>(reader["Id"], 0),
                Book_Club_Id = ConvertDBNull.To<int>(reader["Book_Club_Id"], 0),
                User_Id = ConvertDBNull.To<int>(reader["User_Id"], 0),
                Title = ConvertDBNull.To<string>(reader["Title"], string.Empty),
                Street = ConvertDBNull.To<string>(reader["Street"], string.Empty),
                City = ConvertDBNull.To<string>(reader["City"], string.Empty),
                State = ConvertDBNull.To<string>(reader["State"], string.Empty),
                Zipcode = ConvertDBNull.To<string>(reader["Zipcode"], string.Empty),
                Created = ConvertDBNull.To<DateTime>(reader["Created"], DateTime.MinValue),
            };

        }
        public Meeting SaveMeeting(DTO.Meeting meeting)
        {
            BciDatabase db = new BciDatabase("Bookclub");
            SqlCommand command = BookClubCommandList.SaveMeeting;
            command.Parameters["@Id"].Value = meeting.Id;
            command.Parameters["@Book_Club_Id"].Value = meeting.Book_Club_Id;
            command.Parameters["@User_Id"].Value = meeting.User_Id;
            command.Parameters["@Title"].Value = meeting.Title;
            command.Parameters["@Street"].Value = meeting.Street;
            command.Parameters["@City"].Value = meeting.City;
            command.Parameters["@State"].Value = meeting.State;
            command.Parameters["@Zipcode"].Value = meeting.Zipcode;
            command.Parameters["@Created"].Value = meeting.Created;
            db.ExecuteNonQuery(command);

            meeting.Id = Convert.ToInt32(command.Parameters["@Id"].Value);

            return meeting;
        }

        public BookClub SaveBookClub(DTO.BookClub bookclub)
        {
            BciDatabase db = new BciDatabase("Bookclub");
            SqlCommand command = BookClubCommandList.SaveBookClub;
             command.Parameters["@Id"].Value = bookclub.Id;
            command.Parameters["@Name"].Value = bookclub.Name;
            command.Parameters["@ClubType"].Value = bookclub.ClubType;
            command.Parameters["@GenreType"].Value = bookclub.GenreType;
            command.Parameters["@CreatedDate"].Value = bookclub.CreatedDate;
            command.Parameters["@Description"].Value = bookclub.Description;
            command.Parameters["@Rules"].Value = bookclub.Rules;
            command.Parameters["@Website"].Value = bookclub.Website;
            command.Parameters["@AdultGroup"].Value = bookclub.AdultGroup;
            command.Parameters["@NonLeaderAddEvents"].Value = bookclub.NonLeaderAddEvents;
            command.Parameters["@NonLeaderAddPolls"].Value = bookclub.NonLeaderAddPolls;
            command.Parameters["@NonLeaderAddTopics"].Value = bookclub.NonLeaderAddTopics;
            command.Parameters["@GroupMeetsRealLife"].Value = bookclub.GroupMeetsRealLife;
            command.Parameters["@AvatarURL"].Value = bookclub.AvatarURL;
            db.ExecuteNonQuery(command);

            bookclub.Id = Convert.ToInt32(command.Parameters["@Id"].Value);

            return bookclub;
        }

    }
}
